package com.matt.repository.impl;

import com.matt.commons.utils.QueryUtil;
import com.matt.domain.Children;
import com.matt.domain.ChildrenExtendInfo;
import com.matt.foundation.DO.*;
import com.matt.foundation.constant.CourseConstant;
import com.matt.foundation.query.ChildrenNurseryQuery;
import com.matt.foundation.query.ChildrenQuery;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ChildChildrenExtendRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/9/25
 * *******************************
 */
@Component
public class ChildChildrenExtendRepositoryImpl extends BaseNativeSqlRepository implements ChildChildrenExtendRepository {

    @Override
    public List<ChildrenListDO> findChildrenByNurseryStatus(Integer nurseryStatus, String areaId) {
        String querySql = String.format("select c.id, c.name, c.gender, c.imgUrl " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE  c.aid = '%s'" , areaId);
        List<ChildrenListDO> objectArrayList = sqlObjectList(querySql, ChildrenListDO.class);
        return objectArrayList;
    }

    @Override
    public List<ChildrenListDO> findChildrenByEarlyStatusActive(String areaId) {
        String querySql = String.format("select c.id, c.name, c.gender, c.imgUrl ,c.birthday " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE i.earlyLeftCourseNum > 0 and c.aid = '%s'",  areaId);
        List<ChildrenListDO> objectArrayList = sqlObjectList(querySql, ChildrenListDO.class);
        return objectArrayList;
    }

    @Override
    public List<ChildrenNurseryListDO> findChildByNurseryStatus(ChildrenNurseryQuery query) {
        String querySql = String.format("select c.id as id, c.name as name, c.gender as gender, c.imgUrl as imgUrl, c.month as month, cc.name as className ,i.nurseryEndDate as endDate, c.birthday as birthday " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                " left join rel_classes_children rel on rel.childrenId=c.id left join classes cc on rel.classesId=cc.id " +
                "  WHERE c.aid = '%s'", query.getAreaId());

        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getNurseryStatus())) {
            querySql += String.format(" and i.nurseryStatus='%s' ", query.getNurseryStatus());
        }

        querySql += " order by c.createTime desc ";
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        List<ChildrenNurseryListDO> objectArrayList = sqlObjectList(querySql, ChildrenNurseryListDO.class);
        return objectArrayList;
    }


    @Override
    public Long countChildByNurseryStatus(ChildrenNurseryQuery query) {

        String querySql = String.format("select count(c.id)" +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                " left join rel_classes_children rel on rel.childrenId=c.id left join classes cc on rel.classesId=cc.id " +
                "  WHERE c.aid = '%s'", query.getAreaId());

        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getNurseryStatus())) {
            querySql += String.format(" and i.nurseryStatus='%s' ", query.getNurseryStatus());
        }

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<Object[]> countChildByCheckStatus(String areaId){
        return null;
    }

    @Override
    public List<Object[]> countChildByHealthyStatus(String areaId){
        return null;
    }

    @Override
    public List<ClassChildrenDO> findChildrenClassesByStatus(String areaId, List<String> classIds){

        String querySql = "select c.id, c.name, c.gender, c.imgUrl, c.birthday, " +
                        " cc.id as classesId" +
                        " from children c , children_extend_info i , rel_classes_children rel , classes cc  " +
                        " WHERE c.id=i.childrenId and c.id=rel.childrenId and rel.classesId=cc.id " +
                        " and c.aid = :1 and cc.deleted=false";

        List params = new ArrayList();
        params.add(areaId);
        if (classIds != null && !classIds.isEmpty()){
            querySql += " and cc.id in (:2)";
            params.add(classIds);
        }

        querySql += " order by cc.created desc ";

        List<ClassChildrenDO> objectArrayList = sqlObjectList(querySql, params, ClassChildrenDO.class);

        return objectArrayList;
    }


    @Override
    public List<ChildClassesDO> findChildrenClassesByStatus(String areaId, Integer category) {
        String querySql = String.format("select c.id as id, c.name as name, c.gender as gender, c.imgUrl as imgUrl, cc.name as classesName" +
                " from children c , children_extend_info i , rel_classes_children rel , classes cc  " +
                "  WHERE c.id=i.childrenId and c.id=rel.childrenId and rel.classesId=cc.id " +
                "  and c.aid = '%s' and cc.category ='%s' and cc.deleted=false ", areaId, category);
        if (CourseConstant.Category.NURSERY.codeOf() == category){
            querySql += " and i.nurseryStatus=1 ";
        }else if (CourseConstant.Category.EARLY.codeOf() == category){
            querySql += " and i.earlyLeftCourseNum >0 ";
        }

        querySql += " order by cc.created desc ";
        List<ChildClassesDO> objectArrayList = sqlObjectList(querySql, ChildClassesDO.class);
        return objectArrayList;
    }

    @Override
    public List<ChildClassesDO> findChildrenStatusActive(String areaId) {
        String querySql = String.format("select c.id as id, c.name as name, c.gender as gender, c.imgUrl as imgUrl " +
                " from children c , children_extend_info i  " +
                "  WHERE c.id=i.childrenId and c.aid = '%s' and (i.nurseryStatus=1 or  i.earlyLeftCourseNum>0)", areaId);
        List<ChildClassesDO> objectArrayList = sqlObjectList(querySql, ChildClassesDO.class);
        return objectArrayList;
    }


    @Override
    public List<Object[]> findChildrenBySatActive(Integer contractStatus, String areaId) {
        String querySql = String.format("select c.id, c.name, c.gender, c.imgUrl, c.status " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE i.earlyLeftCourseNum > 0 and c.aid = '%s' and c.status = 1 ", areaId);
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }

    @Override
    public List<Object[]> findChildrenByCBNonBind(String areaId, String name) {
        String querySql = String.format("select c.id, c.name, c.gender, c.imgUrl " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE i.earlyLeftCourseNum > 0 and i.evaluatingCBBindStatus = 0 and c.aid = '%s'", areaId);
        if (StringUtils.isNotBlank(name)) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", name, "%");
        }
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }

    @Override
    public List<Children> findChildrenByCondition(ChildrenQuery query) {
        String querySql = String.format("select c.id as id, c.name as name, c.nickName as nickName, c.gender as gender, c.aid as areaId, c.address as address " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE true and c.status =1 ");
        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getActive())) {
//            querySql += String.format(" and (i.nurseryStatus='%s' or i.earlyTotalCourseNum > '%s')", 1, 0);
        }

        if(StringUtils.isNotBlank(query.getClassName())){
            querySql += " and c.id in (SELECT r.childrenId from rel_classes_children r  " +
                    "left JOIN classes cl on r.classesId = cl.id  " +
                    String.format("where cl.areaId = '%s'  and cl.name like concat('%%', '%s', '%%')) ", query.getAreaId(), query.getClassName());
        }

        if (Objects.nonNull(query.getAreaId())) {
            querySql += String.format(" and c.aid='%s' ", query.getAreaId());
        }

        if (Objects.nonNull(query.getChildrenId())) {
            querySql += String.format(" and c.id='%s' ", query.getChildrenId());
        }
        if(org.apache.commons.lang3.StringUtils.isNotBlank(query.getOrderBy())){
            if(org.apache.commons.lang3.StringUtils.isBlank(query.getSequence()) || "asc".equalsIgnoreCase(query.getSequence())){
                querySql += String.format(" order by c.%s desc ", QueryUtil.replaceAllSql(query.getOrderBy()));
            }else{
                querySql += String.format(" order by c.%s desc ", QueryUtil.replaceAllSql(query.getOrderBy()));
            }
        }else {
            querySql += " order by c.createTime desc ";
        }
        querySql += String.format(" limit %s, %s ", query.getPageNum() , query.getPageSize());
        List<Children> objectArrayList = sqlObjectList(querySql, Children.class);
        return objectArrayList;
    }

    @Override
    public Long countChildrenByCondition(ChildrenQuery query) {
        String querySql = String.format("select count(c.id) " +
                " from children c LEFT join children_extend_info i on c.id = i.childrenId " +
                "  WHERE true and c.status =1  ");
        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getActive())) {
//            querySql += String.format(" and (i.nurseryStatus='%s' or i.earlyTotalCourseNum > '%s')", 1, 0);
        }

        if (Objects.nonNull(query.getAreaId())) {
            querySql += String.format(" and c.aid='%s' ", query.getAreaId());
        }

        if (Objects.nonNull(query.getChildrenId())) {
            querySql += String.format(" and c.id='%s' ", query.getChildrenId());
        }

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public ChildrenExtendInfo findCameraTime(String crmClienteleId) {
        String querySql = String.format("SELECT cei.id,cei.childrenId,IFNULL(cei.cameraValidTo,NOW()) as cameraValidTo  FROM children c INNER JOIN children_extend_info cei on c.id  = cei.childrenId WHERE c.crmClienteleId = '%s'", crmClienteleId);
        List<ChildrenExtendInfo> objectArrayList = sqlObjectList(querySql, ChildrenExtendInfo.class);
        return objectArrayList.get(0);
    }
}
